{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#Vertica\n",
    "This quick introduction to using Vertica inside IPython starts with a [datarama blog post](https://sites.google.com/a/twitter.com/gnip-data-rama/2015-q1-data-blog/2015-02-19twitterdataresourcespart5vertica). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "from vertica_python import connect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# create connection\n",
    "connection = connect({\n",
    "    'host': '127.0.0.1',\n",
    "    'port': 5434,\n",
    "    'user': '<usrname>',\n",
    "    'password': '<pswd>',\n",
    "    'database': '<db>'\n",
    "    })\n",
    "\n",
    "# SQL strings are executed within the cursor\n",
    "cur = connection.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Build New Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# build new table\n",
    "cur.execute(r\"\"\"DROP TABLE IF EXISTS test.ddis_blehman_test\"\"\")\n",
    "cur.execute(r\"\"\"CREATE TABLE test.ddis_blehman_test \n",
    "(\n",
    "userID INT\n",
    ", endDate_oldCarrier TIMESTAMP\n",
    ", startDate_newCarrier TIMESTAMP\n",
    ", oldCarrier VARCHAR(50)\n",
    ", newCarrier VARCHAR(50)\n",
    ", days_oldCarrier INT\n",
    ", days_newCarrier INT\n",
    ", ratio FLOAT\n",
    ")\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Insert Data Into Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# insert data to table\n",
    "cur.execute(r\"\"\"INSERT INTO test.ddis_blehman_test VALUES \n",
    "(\n",
    "'158871820'\n",
    ", '2014-10-20 17:00:00'\n",
    ", '2014-10-21 05:00:00'\n",
    ", 'Verizon'\n",
    ", 'AT&T'\n",
    ", '97'\n",
    ", '91'\n",
    ", '0.990611'\n",
    ")\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Print records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print records as list\n",
    "cur = connection.cursor('list') # list is the default behavior\n",
    "cur.execute(r\"\"\"SELECT * FROM test.ddis_blehman_test\"\"\")\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# print records as dict\n",
    "cur = connection.cursor('dict')\n",
    "cur.execute(r\"\"\"SELECT * FROM test.ddis_blehman_test\"\"\")\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Iterate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cur = connection.cursor()\n",
    "cur.execute(r\"\"\"SELECT * FROM test.ddis_blehman_test\"\"\")\n",
    "for row in cur.iterate():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Close Connection"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# manual close\n",
    "connection.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
